/*DONE* Table [dbo].[<CompanyName>$Transaction Header$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 30.7.2020 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Transaction Header$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Transaction Header$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Store No_] [nvarchar](10) NOT NULL,
	[POS Terminal No_] [nvarchar](10) NOT NULL,
	[Transaction No_] [int] NOT NULL,
	[Transaction Type] [int] NOT NULL,
	[Receipt No_] [nvarchar](20) NOT NULL,
	[VAT Bus_Posting Group] [nvarchar](20) NOT NULL,
	[Created on POS Terminal] [nvarchar](10) NOT NULL,
	[Staff ID] [nvarchar](20) NOT NULL,
	[Date] [datetime] NOT NULL,
	[Original Date] [datetime] NOT NULL,
	[Time] [datetime] NOT NULL,
	[Shift No_] [nvarchar](1) NOT NULL,
	[Shift Date] [datetime] NOT NULL,
	[Wrong Shift] [tinyint] NOT NULL,
	[Infocode Disc_ Group] [nvarchar](20) NOT NULL,
	[Customer No_] [nvarchar](20) NOT NULL,
	[Transaction Code] [int] NOT NULL,
	[Trans_ Sale_Pmt_ Diff_] [decimal](38, 20) NOT NULL,
	[Net Amount] [decimal](38, 20) NOT NULL,
	[Cost Amount] [decimal](38, 20) NOT NULL,
	[Gross Amount] [decimal](38, 20) NOT NULL,
	[Payment] [decimal](38, 20) NOT NULL,
	[Discount Amount] [decimal](38, 20) NOT NULL,
	[Customer Discount] [decimal](38, 20) NOT NULL,
	[Total Discount] [decimal](38, 20) NOT NULL,
	[No_ of Items] [decimal](38, 20) NOT NULL,
	[Amount to Account] [decimal](38, 20) NOT NULL,
	[Rounded] [decimal](38, 20) NOT NULL,
	[Customer Disc_ Group] [nvarchar](20) NOT NULL,
	[Entry Status] [int] NOT NULL,
	[No_ of Invoices] [int] NOT NULL,
	[No_ of Item Lines] [decimal](38, 20) NOT NULL,
	[Statement Code] [nvarchar](20) NOT NULL,
	[Statement No_ - NOT USED] [nvarchar](20) NOT NULL,
	[Refund Receipt No_] [nvarchar](20) NOT NULL,
	[Income_Exp_ Amount] [decimal](38, 20) NOT NULL,
	[To Account] [tinyint] NOT NULL,
	[No_ of Payment Lines] [decimal](38, 20) NOT NULL,
	[Sale Is Return Sale] [tinyint] NOT NULL,
	[Counter] [int] NOT NULL,
	[Time when Total Pressed] [datetime] NOT NULL,
	[Time when Trans_ Closed] [datetime] NOT NULL,
	[Trans_ Currency] [nvarchar](10) NOT NULL,
	[Items Posted] [tinyint] NOT NULL,
	[Post as Shipment] [tinyint] NOT NULL,
	[Safe Entry No_] [int] NOT NULL,
	[Safe Code] [nvarchar](10) NOT NULL,
	[Manager ID] [nvarchar](20) NOT NULL,
	[Table No_] [int] NOT NULL,
	[No_ of Covers] [decimal](38, 20) NOT NULL,
	[Split Number] [int] NOT NULL,
	[Comment] [nvarchar](100) NOT NULL,
	[Sell-to Contact No_] [nvarchar](20) NOT NULL,
	[Gift Registration No_] [nvarchar](20) NOT NULL,
	[Member Card No_] [nvarchar](100) NOT NULL,
	[Sales Type] [nvarchar](20) NOT NULL,
	[Starting Point Balance] [decimal](38, 20) NOT NULL,
	[Contains Forecourt Items] [tinyint] NOT NULL,
	[Apply to Doc_ No_] [nvarchar](20) NOT NULL,
	[Source Type] [int] NOT NULL,
	[Open Drawer] [tinyint] NOT NULL,
	[Replicated] [tinyint] NOT NULL,
	[Replication Counter] [int] NOT NULL,
	[Retrieved from Receipt No_] [nvarchar](20) NOT NULL,
	[Z-Report ID] [nvarchar](10) NOT NULL,
	[Y-Report ID] [nvarchar](10) NOT NULL,
	[Customer Order ID] [nvarchar](20) NOT NULL,
	[Playback Recording ID] [nvarchar](30) NOT NULL,
	[Playback Entry No_] [int] NOT NULL,
	[BI Timestamp] [datetime] NOT NULL,
	[Customer Order] [tinyint] NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Trans_ Is Mixed Sale_Refund] [tinyint] NOT NULL,
	[Reverted Gross Amount] [decimal](38, 20) NOT NULL,
	[Tax Area Code] [nvarchar](20) NOT NULL,
	[WIC Transaction] [tinyint] NOT NULL,
	[Tax Liable] [tinyint] NOT NULL,
	[Tax Exemption No_] [nvarchar](30) NOT NULL,
	[Net Income_Exp_ Amount] [decimal](38, 20) NOT NULL,
 CONSTRAINT ['+@Company+'$Transaction Header$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Store No_] ASC,
	[POS Terminal No_] ASC,
	[Transaction No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Transaction Header$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END